package org.jeecgframework.web.service.impl;

import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.web.service.CbsEquipmentApplyServiceI;
import org.jeecgframework.web.service.CbsServiceAssignListServiceI;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

@Service("cbsEquipmentApplyService")
@Transactional
public class CbsEquipmentApplyServiceImpl extends CommonServiceImpl implements CbsEquipmentApplyServiceI {
    @Autowired
    SystemService systemService;
    @Autowired
    private CbsServiceAssignListServiceI cbsServiceAssignListService;
    private static Logger log = Logger.getLogger(CbsEquipmentApplyServiceImpl.class);

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsEquipmentApplyEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsEquipmentApplyEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsEquipmentApplyEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @param
     * @return
     */
    public boolean doAddSql(CbsEquipmentApplyEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @param
     * @return
     */
    public boolean doUpdateSql(CbsEquipmentApplyEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @param
     * @return
     */
    public boolean doDelSql(CbsEquipmentApplyEntity t) {
        return true;
    }

    @Override
    public List<CbsEquipmentApplyDetailEntity> getAllCbsEqipmentByEquipmentId(String id) {
        List<CbsEquipmentApplyDetailEntity> cbsEquipmentApplyDetailHql = findHql("from CbsEquipmentApplyDetailEntity where applyId.id='" + id + "'");
        return cbsEquipmentApplyDetailHql;
    }

    @Override
    public CbsEquipmentApplyDetailEntity getAllCbsEqipmentByDetailId(String id) {
        CbsEquipmentApplyDetailEntity cbsEquipmentApplyDetailEntity = get(CbsEquipmentApplyDetailEntity.class, id);
        return cbsEquipmentApplyDetailEntity;
    }

    @Override
    public void sendEmailToCSA(String id) throws Exception{
        List<CbsDelinerEntity> cbsDelinerHql = systemService.findHql("from CbsDelinerEntity where status=1 and equipmentApplyId.id = '" + id + "' order by createDate desc");
        CbsEquipmentApplyEntity cbsEquipmentApply = systemService.getEntity(CbsEquipmentApplyEntity.class, id);
        List<CbsServiceEntity> cbsServiceList = systemService.findHql("from CbsServiceEntity where id='" + cbsEquipmentApply.getServiceId() + "'");
        List<TSUser> hql = systemService.findHql("from TSUser where id='" + cbsEquipmentApply.getApproveUser() + "'");
        List<String> listbySql = systemService.findListbySql("select email from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
        List<String> listphonebySql = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
        List<String> mobilePhoneList = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_base_user bu on u.id = bu.id where bu.username = '" + cbsEquipmentApply.getCreateBy() + "'");
        if ("1".equals(cbsDelinerHql.get(0).getStatus())) {
            Calendar now = Calendar.getInstance();
            //一周第一天是否为星期天
            boolean isFirstSunday = (now.getFirstDayOfWeek() == Calendar.SUNDAY);
            int weekDay = now.get(Calendar.DAY_OF_WEEK);
            //若一周第一天为星期天，则-1
            if (isFirstSunday) {
                weekDay = weekDay - 1;
                if (weekDay == 0) {
                    weekDay = 7;
                }
            }
            int hour = now.get(Calendar.HOUR_OF_DAY);
            if (weekDay == 7 || weekDay == 6 || hour < 8 || hour >= 18) {
                for (int i = 0; i < listphonebySql.size(); i++) {
                    SendMessage.sendSMSWithContent(listphonebySql.get(i), "CSA同事们，请安排以下发货。发货单号：<" + cbsDelinerHql.get(0).getCode() + ">。备件审批人：<" + hql.get(0).getRealName() + " " + hql.get(0).getMobilePhone() + ">。具体内容请查看CBS系统", systemService);
                }
            }

            //获取服务商信息
            List<String> shortnameList = systemService.findListbySql("select d.shortname from t_s_depart d left join cbs_service cbs on d.org_code = cbs.service_provider_code where cbs.id='" + cbsEquipmentApply.getServiceId() + "'");
            String shortname = "";
            if (shortnameList.size() != 0) {
                shortname = shortnameList.get(0);
            }
            String emailTital = "备件发货申请：<" + cbsDelinerHql.get(0).getCode() + ">-<" + cbsServiceList.get(0).getLevel() + ">-<" + cbsServiceList.get(0).getCode() + ">-<" + cbsServiceList.get(0).getZyCinemaName() + ">-" + getDateStr(new Date());
            String emailContent = "Dear CSA同事们，请安排以下发货。\n" +
                    "发货单号：<" + cbsDelinerHql.get(0).getCode() + ">。\n" +
                    "备件申请人：<" + shortname + "> <" + cbsEquipmentApply.getCreateName() + "> <" + mobilePhoneList.get(0) + ">。\n" +
                    "备件审批人： <" + hql.get(0).getRealName() + "> <" + hql.get(0).getMobilePhone() + ">。\n" +
                    "具体内容请查看http://cbs.cfg-barco.com\n" +
                    "【中影巴可客服部】\n" +
                    "* 该邮件请勿回复";
            for (int i = 0; i < listbySql.size(); i++) {
                SendMailUtil.sendeMail(listbySql.get(i), emailTital, emailContent, systemService);
            }
        }
    }

    @Override
    public void exportXLS(HttpServletRequest request, HttpServletResponse response) {
        OutputStream out = null;
        try {
            String fileName = "备件异常.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] planIdsArr = oConvertUtils.getString(request.getParameter("planIds")).split(",");
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n");
            sql.append("	l.id AS id,\n");
            sql.append("	IFNULL(cbs.`record_id`, '') AS CODE,\n");
            sql.append("	IFNULL(l.sn, '') AS sn,\n");
            sql.append("	CASE l.is_close WHEN '1' then '未处理' WHEN '2' then '已处理' END AS isClose,\n");
            sql.append("	IFNULL(l.ABNORMAL_CAUSE, '') AS abnormalCause,\n");
            sql.append("	IFNULL(l.description, '') AS description,\n");
            sql.append("	IFNULL(l.create_date, '') AS createDate,\n");
            sql.append("	IFNULL(l.update_by, '') AS updateBy,\n");
            sql.append("	IFNULL(l.update_date, '') AS updateDate,\n");
            sql.append("	IFNULL(l.ABNORMAL_RESULT, '') AS abnormalResult,\n");
            sql.append("	IFNULL(l.need_system_revise, '') AS NeedSystemRevise,\n");
            sql.append("	IFNULL(l.admin_description, '') AS AdminDescription,\n");
            sql.append("	IFNULL(l.admin_updateBy, '') AS AdminUpdateBy,\n");
            sql.append("	IFNULL(l.admin_updateDate, '') AS AdminUpdateDate,\n");
            sql.append("	IFNULL(l.admin_abnormal_result, '') AS AdminAbnormalResult\n");
            sql.append("FROM\n");
            sql.append("	cbs_equipment_error_log l\n");
            sql.append("LEFT JOIN CBS_EQUIPMENT_APPLY cbs ON cbs.id = l.CBS_EQUIPMENT_APPLY_ID");
            sql.append("   WHERE ");
            sql.append(" l.id IN ( ");
            for (int i = 0; i < planIdsArr.length; i++) {
                if (i == planIdsArr.length - 1) {
                    sql.append("'" + planIdsArr[i] + "'");
                } else {
                    sql.append("'" + planIdsArr[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append("GROUP BY l.create_date");
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            TSUser user = ResourceUtil.getSessionUserName();
            systemService.addLog(user.getRealName() + "导出" + reusltMaps.size() + "条备件异常记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);

            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/equipmentErrorexportXLS.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void exportEquipmentXls(HttpServletRequest request, HttpServletResponse response) {
        OutputStream out = null;
        try {
            String fileName = "备件审批.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] planIdsArr = oConvertUtils.getString(request.getParameter("planIds")).split(",");
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n" +
                    "\tifnull(ea.id, '') AS id,\n" +
                    "\tifnull(ea.record_id, '') AS recordId,\n" +
                    "\tifnull(s.`code`, '') AS CODE,\n" +
                    "\tifnull(s.sn, '') AS sn,\n" +
                    "\tifnull(s.sr, '') AS sr,\n" +
                    "\tifnull(s.so, '') AS so,\n" +
                    "\tifnull(\n" +
                    "\t\tGROUP_CONCAT(ead.type_des SEPARATOR '\\\\'),\n" +
                    "\t\t''\n" +
                    "\t) AS typeDes,\n" +
                    "\tifnull(\n" +
                    "\t\tGROUP_CONCAT(ead.item SEPARATOR '\\\\'),\n" +
                    "\t\t''\n" +
                    "\t) AS item,\n" +
                    "\tifnull(\n" +
                    "\t\tGROUP_CONCAT(ead.total SEPARATOR '\\\\'),\n" +
                    "\t\t''\n" +
                    "\t) AS total,\n" +
                    "\tifnull(\n" +
                    "\t\tGROUP_CONCAT(tst.typename SEPARATOR '\\\\'),\n" +
                    "\t\t''\n" +
                    "\t) AS applyLayer,\n" +
                    "\tifnull(ea.create_name, '') AS createName,\n" +
                    "\tifnull(ea.create_date, '') AS createDate,\n" +
                    "\tcase ea.status when '1' then '未审批' when '2' then '已通过' when '3' then '未通过' END AS status\n" +
                    "FROM\n" +
                    "\tcbs_equipment_apply ea\n" +
                    "LEFT JOIN cbs_equipment_apply_detail ead ON ea.id = ead.apply_id\n" +
                    "LEFT JOIN cbs_service s ON s.id = ea.service_id\n" +
                    "LEFT JOIN t_s_type tst ON tst.typecode = ead.apply_layer\n" +
                    "LEFT JOIN t_s_typegroup tstp ON tstp.id = tst.typegroupid\n" +
                    "WHERE\n" +
                    "\ttstp.typegroupcode = 'apply_layer' and ea.id in (");
            for (int i = 0; i < planIdsArr.length; i++) {
                if (i == planIdsArr.length - 1) {
                    sql.append("'" + planIdsArr[i] + "'");
                } else {
                    sql.append("'" + planIdsArr[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append("GROUP BY\n" +
                    "\tea.id\n" +
                    "\t\tORDER BY\n" +
                    "\t\tSTATUS DESC,createDate DESC");
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            TSUser user = ResourceUtil.getSessionUserName();
            systemService.addLog(user.getRealName() + "导出" + reusltMaps.size() + "条备件审批记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);

            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/equipmentexportXLS.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*每10分钟执行一次，检查备件申请状态，超过两小时未审批的置成已通过*/
    @Override
    public void getGetEquipmentApply() {
        try {
            updateBySqlString("update cbs_equipment_apply set sign_time = timestampdiff(Minute,create_date,NOW()) where status = '" + StaticValue.wsp + "'");
            List<CbsEquipmentApplyEntity> cbsEquipmentApplyList = systemService.findHql("from CbsEquipmentApplyEntity where status='" + StaticValue.wsp + "' and judge = '1' order by signTime");
            if (cbsEquipmentApplyList != null && cbsEquipmentApplyList.size() != 0) {
                for (int i = 0; i < cbsEquipmentApplyList.size(); i++) {
                    //查申请人ID
                    List<String> idList = systemService.findListbySql("select id from t_s_base_user where username = '" + cbsEquipmentApplyList.get(i).getCreateBy() + "'");
                    //待审批
                    if (cbsEquipmentApplyList.get(i).getSignTime() >= 120) {
                        //系统自动通过该备件申请，通过备注为“系统自动审批通过”
                        cbsEquipmentApplyList.get(i).setStatus(2);
                        cbsEquipmentApplyList.get(i).setUpdateDate(new Date());
                        cbsEquipmentApplyList.get(i).setMemo("系统自动审批通过");
                        cbsEquipmentApplyList.get(i).setApproveUser(idList.get(0));
                        systemService.saveOrUpdate(cbsEquipmentApplyList.get(i));
                        //系统自动创建发货单
                        cbsServiceAssignListService.autoDoAdd(cbsEquipmentApplyList.get(i).getId(), cbsEquipmentApplyList.get(i).getCreateName());

                        //自动审批通过，备件添加日志
                        CbsHandleLogEntity cbsHandleLog = new CbsHandleLogEntity();
                        cbsHandleLog.setBusinessId(cbsEquipmentApplyList.get(i).getId());
                        cbsHandleLog.setLogType(1);
                        cbsHandleLog.setDescription("系统自动审批通过备件申请");
                        cbsHandleLog.setCreateDate(new Date());
                        systemService.save(cbsHandleLog);

                        /*备件申请审批通过,向CSA发送短信和邮件通知*/
                        sendEmailToCSA(cbsEquipmentApplyList.get(i).getId());
                        /*备件申请审批通过后两个小时后，发货单状态仍是“CSA待发货”*/
                        selectStatus1(cbsEquipmentApplyList.get(i).getId());
                        sendEmailToSFDC(cbsEquipmentApplyList.get(i));
                    }
                }
            }
            List<CbsEquipmentApplyEntity> cbsEquipmentApplyListEnginer = systemService.findHql("from CbsEquipmentApplyEntity where status='" + StaticValue.wsp + "' and judge = '2' and (isSend!='1' or isSend is null) order by signTime");
            if (cbsEquipmentApplyListEnginer != null && cbsEquipmentApplyListEnginer.size() != 0) {
                for (int i = 0; i < cbsEquipmentApplyListEnginer.size(); i++) {
                    CbsEquipmentApplyEntity cbsEquipmentApplyEntity = cbsEquipmentApplyListEnginer.get(i);
                    //待审批
                    if (cbsEquipmentApplyListEnginer.get(i).getSignTime() >= 120) {
                        CbsServiceEntity cbsServiceEntity = systemService.getEntity(CbsServiceEntity.class, cbsEquipmentApplyEntity.getServiceId());
                        List<String> shortname = systemService.findListbySql("select d.shortname from t_s_depart d left join cbs_service cbs on d.org_code = cbs.service_provider_code where cbs.id='" + cbsServiceEntity.getId() + "'");
                        List<ZyEngineerEntity> zyEngineerHql = systemService.findHql("from ZyEngineerEntity where id = '" + cbsServiceEntity.getEngineer() + "'");
                        String shortnames = "";
                        if (shortname.size() > 0) {
                            shortnames = shortname.get(0);
                        }
                        String emailTital = "备件申请审批超时提醒 :<" + shortnames + ">-<" + cbsEquipmentApplyEntity.getRecordId() + ">-<" + cbsServiceEntity.getLevel() + ">-<" + cbsServiceEntity.getServiceType1().getName() + "," + cbsServiceEntity.getServiceType2().getName() + ">-<" + cbsServiceEntity.getCode() + ">" + "-" + getDateStr(new Date());
                        String zyname = "";
                        String zyPhone = "";
                        if (zyEngineerHql.size() > 0) {
                            zyname = zyEngineerHql.get(0).getName();
                            zyPhone = zyEngineerHql.get(0).getCellphone();
                        }
                        String emailContent = "400同事你好，备件申请单号:<" + cbsEquipmentApplyEntity.getRecordId() + ">尚未进行审批，请提醒相关人员及时审批。\n" + "提交工程师：<" +
                                shortnames + ">" + "<" + zyname + ">" + "<" + zyPhone + ">" + "具体内容请查看http://cbs.cfg-barco.com\n" +
                                "\n" +
                                "* 该邮件请勿回复";

                        //查找所有HD主管 角色，正常状态下，邮件，电话
                        List<String> listbySql = systemService.findListbySql("select email from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "'and r.rolecode='" + StaticValue.RULE_CODE_BARCO_HD_Manager + "'");
                        List<String> listbySql1 = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "'and r.rolecode='" + StaticValue.RULE_CODE_BARCO_HD_Manager + "'");
                        if (listbySql.size() != 0) {
                            for (int j = 0; j < listbySql.size(); j++) {
                                SendMailUtil.sendeMail(listbySql.get(j), emailTital, emailContent, systemService);
                            }
                        }
                        if (listbySql1.size() != 0) {
                            for (int j = 0; j < listbySql1.size(); j++) {
                                SendMessage.sendSMSWithContent(listbySql1.get(j), "备件申请号(" + cbsEquipmentApplyEntity.getRecordId() + ")尚未进行审批，请提醒相关人员及时审批。请及时查看CBS系统", systemService);
                            }
                        }
                        systemService.updateBySqlString("update cbs_equipment_apply set is_send='1' where id='" + cbsEquipmentApplyEntity.getId() + "'");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); //此处采用手动回滚
        }
    }

    @Override
    public AjaxJson pass(String so, String id, String csaAttention, String engineerAttention) {
        AjaxJson j = new AjaxJson();
        String message = "备件审批成功";
        try {
            TSUser user = ResourceUtil.getSessionUserName();
            CbsEquipmentApplyEntity cbsEquipmentApply = this.getEntity(CbsEquipmentApplyEntity.class, id);
            if (StringUtil.isNotEmpty(cbsEquipmentApply)) {
                if (cbsEquipmentApply.getStatus() != 2) {

                    cbsEquipmentApply.setApproveUser(user.getId());
                    cbsEquipmentApply.setStatus(2);
                    cbsEquipmentApply.setCsaAttention(csaAttention);
                    cbsEquipmentApply.setEngineerAttention(engineerAttention);
                    this.saveOrUpdate(cbsEquipmentApply);

                    if (StringUtil.isNotEmpty(so)) {
                        systemService.updateBySqlString("update cbs_service set so='" + so + "'" + "where id='" + cbsEquipmentApply.getServiceId() + "'");
                    }

                    /*APP通知*/
                    CbsAppMessageEntity appMessage = new CbsAppMessageEntity();
                    List<String> listbySql = systemService.findListbySql("select id from t_s_base_user where username = '" + cbsEquipmentApply.getCreateBy() + "'");
                    String content = "您的备件申请" + cbsEquipmentApply.getRecordId() + "已审批通过，请等待物流安排发货。";
                    appMessage.setContent(content);
                    appMessage.setSender(user.getId());
                    appMessage.setReciver(listbySql.get(0));
                    appMessage.setState(StaticValue.DIC_APP_MESSAGE_STATE_UNREAD);
                    systemService.save(appMessage);
                    systemService.addLog("新增APP消息,接受方:" + listbySql.get(0) + ",内容:" + content, Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);

                    //如果该备件申请关联的服务单没有SR号，则向SFDC发送创单邮件
                    sendEmailToSFDC(cbsEquipmentApply);
                    //加入日志
                    CbsHandleLogEntity cbsHandleLog = new CbsHandleLogEntity();
                    cbsHandleLog.setBusinessId(id);
                    cbsHandleLog.setLogType(1);
                    cbsHandleLog.setDescription("通过了备件申请");
                    systemService.save(cbsHandleLog);
                    //自动创建发货单
                    cbsServiceAssignListService.autoDoAdd(id, user.getRealName());
                    /*备件申请审批通过,向CSA发送短信和邮件通知*/
                    this.sendEmailToCSA(id);
                    /*备件申请审批通过后两个小时（该时限可以人为设置）后，发货单状态仍是“CSA待发货”，则向CSA发送短信和邮件通知。*/
                    selectStatus(id);
                    message = cbsEquipmentApply.getRecordId();
                    j.setSuccess(true);
                } else {
                    message = "备件已经审批，请刷新页面再进行操作";
                    j.setSuccess(false);
                }
            }
        } catch (Exception e) {
            j.setSuccess(false);
            message = "备件审批失败，事务回滚";
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); //此处采用手动回滚
        }
        j.setMsg(message);
        return j;
    }

    @Override
    public AjaxJson notPass(String id, String reasion) {
        AjaxJson j = new AjaxJson();
        String message = "备件审批成功";
        boolean flag = true;
        TSUser user = ResourceUtil.getSessionUserName();
        try {
            if (StringUtil.isNotEmpty(id)) {
                CbsEquipmentApplyEntity cbsEquipmentApply = this.getEntity(CbsEquipmentApplyEntity.class, id);
                if (cbsEquipmentApply.getStatus() != 3) {
                    cbsEquipmentApply.setStatus(3);
                    cbsEquipmentApply.setReasion(reasion);
                    cbsEquipmentApply.setApproveUser(user.getId());
                    this.saveOrUpdate(cbsEquipmentApply);
                    /*APP通知*/
                    CbsAppMessageEntity appMessage = new CbsAppMessageEntity();
                    List<String> listbySql = systemService.findListbySql("select id from t_s_base_user where username = '" + cbsEquipmentApply.getCreateBy() + "'");
                    String content = "您的备件申请" + cbsEquipmentApply.getRecordId() + "审批未通过，原因:<" + cbsEquipmentApply.getReasion() + ">。\n" +
                            "审批人： <" + user.getRealName() + "> <" + user.getMobilePhone() + ">";
                    appMessage.setContent(content);
                    appMessage.setSender(user.getId());
                    appMessage.setReciver(listbySql.get(0));
                    appMessage.setState(StaticValue.DIC_APP_MESSAGE_STATE_UNREAD);
                    systemService.save(appMessage);
                    systemService.addLog("新增APP消息,接受方:" + listbySql.get(0) + ",内容:" + content, Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);
                    String messageContent = "您的备件申请<" + cbsEquipmentApply.getRecordId() + ">审批未通过，原因:<" + cbsEquipmentApply.getReasion() + ">。审批人： <" + user.getRealName() + "> <" + user.getMobilePhone() + ">。详情请查看CBS系统";
                    //申请人电话
                    List<String> mobilePhone = systemService.findListbySql("select mobilePhone from t_s_user where id in (select id from t_s_base_user where username='" + cbsEquipmentApply.getCreateBy() + "')");
                    SendMessage.sendSMSWithContent(mobilePhone.get(0), messageContent, systemService);
                    //加入日志
                    CbsHandleLogEntity cbsHandleLog = new CbsHandleLogEntity();
                    cbsHandleLog.setBusinessId(id);
                    cbsHandleLog.setLogType(1);
                    cbsHandleLog.setDescription("备件申请未通过。"+(StringUtil.isNotEmpty(reasion)?"原因："+reasion:""));
                    systemService.save(cbsHandleLog);
                    message = cbsEquipmentApply.getRecordId();
                    // add start 20190621 未通过时取消备件异常 Amy
                    systemService.updateBySqlString("update cbs_equipment_error_log set description = '备件申请未通过',abnormal_result='备件申请未通过',need_system_revise = '2', is_close = 2, update_name='system',update_date=NOW() where cbs_equipment_apply_id = '"+id+"' and is_close = 1 ");
                    // add end 20190621 未通过时取消备件异常 Amy
                } else {
                    flag = false;
                    message = "备件已被审批，请刷新页面后再操作";
                }
            }
        } catch (Exception e) {
            message = "审批异常";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        j.setSuccess(flag);
        j.setMsg(message);
        return j;
    }

    /*备件申请审批通过后两个小时（该时限可以人为设置）后，发货单状态仍是“CSA待发货”，则向CSA发送短信和邮件通知。*/
    public void selectStatus(final String id) {
        Timer timer = new Timer();
        timer.schedule(new TimerTask() {
            public void run() {
                List<CbsDelinerEntity> cbsDelinerHql = systemService.findHql("from CbsDelinerEntity where status=1 and equipmentApplyId.id = '" + id + "' order by createDate desc");
                if (cbsDelinerHql.size() != 0) {
                    CbsEquipmentApplyEntity cbsEquipmentApply = systemService.getEntity(CbsEquipmentApplyEntity.class, id);
                    //获取服务商信息
                    List<String> shortnames = systemService.findListbySql("select d.shortname from t_s_depart d left join cbs_service cbs on d.org_code = cbs.service_provider_code where cbs.id='" + cbsEquipmentApply.getServiceId() + "'");
                    List<CbsServiceEntity> cbsServiceList = systemService.findHql("from CbsServiceEntity where id='" + cbsEquipmentApply.getServiceId() + "'");
                    List<TSUser> hql = systemService.findHql("from TSUser where id='" + cbsEquipmentApply.getApproveUser() + "'");
                    List<String> mobilePhoneList = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_base_user bu on u.id = bu.id where bu.username = '" + cbsEquipmentApply.getCreateBy() + "'");
                    if (StaticValue.CSAdfh.equals(cbsDelinerHql.get(0).getStatus())) {
                        String shortname = "";
                        if (shortnames.size() != 0) {
                            shortname = shortnames.get(0);
                        }
                        String emailTital = "备件发货超时提醒：<" + cbsDelinerHql.get(0).getCode() + ">-<" + cbsServiceList.get(0).getLevel() + ">-<" + cbsServiceList.get(0).getCode() + ">-<" + cbsServiceList.get(0).getZyCinemaName() + ">-" + getDateStr(new Date());
                        String emailContent = "Dear CSA同事们，发货单:<" + cbsDelinerHql.get(0).getCode() + ">尚未发货，请及时处理。\n" +
                                "发货单号：<" + cbsDelinerHql.get(0).getCode() + ">。\n" +
                                "备件申请人：<" + shortname + "> <" + cbsEquipmentApply.getCreateName() + "> <" + mobilePhoneList.get(0) + ">。\n" +
                                "备件审批人： <" + hql.get(0).getRealName() + "> <" + hql.get(0).getMobilePhone() + ">。\n" +
                                "具体内容请查看http://cbs.cfg-barco.com\n" +
                                "【中影巴可客服部】\n" +
                                "* 该邮件请勿回复";
                        List<String> listbySql = systemService.findListbySql("select email from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
                        for (int i = 0; i < listbySql.size(); i++) {
                            SendMailUtil.sendeMail(listbySql.get(i), emailTital, emailContent, systemService);
                        }
                    }
                    List<String> listbySql1 = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
                	// add start 190125 Amy 9点30之前和21点之后不提醒
                	Calendar calendar = Calendar.getInstance();
                    calendar.setTime(new Date());
                    int hour = calendar.get(Calendar.HOUR_OF_DAY);
                    int minutes = calendar.get(Calendar.MINUTE);
                	if (hour < 9 || hour >= 21 || (hour == 9 && minutes < 30)) {
                	} else {
	                    for (int i = 0; i < listbySql1.size(); i++) {
	                        SendMessage.sendSMSWithContent(listbySql1.get(i), "发货单号<" + cbsDelinerHql.get(0).getCode() + ">尚未发货，请及时处理。详情请查看CBS系统", systemService);
	                    }
                	}
                	// add end 190125 Amy 9点30之前和21点之后不提醒
                }
            }
        }, StaticValue.selectStatusTiem);
    }

    /*备件申请审批通过后两个小时（该时限可以人为设置）后，发货单状态仍是“CSA待发货”，则向CSA发送短信和邮件通知。*/
    public void selectStatus1(final String id) {
        Timer timer = new Timer();
        timer.schedule(new TimerTask() {
            public void run() {
                List<CbsDelinerEntity> cbsDelinerHql = systemService.findHql("from CbsDelinerEntity where status=1 and equipmentApplyId.id = '" + id + "' order by createDate desc");
                if (cbsDelinerHql.size() != 0) {
                    CbsEquipmentApplyEntity cbsEquipmentApply = systemService.getEntity(CbsEquipmentApplyEntity.class, id);
                    //获取服务商信息
                    List<String> shortnameList = systemService.findListbySql("select d.shortname from t_s_depart d left join cbs_service cbs on d.org_code = cbs.service_provider_code where cbs.id='" + cbsEquipmentApply.getServiceId() + "'");
                    List<CbsServiceEntity> cbsServiceList = systemService.findHql("from CbsServiceEntity where id='" + cbsEquipmentApply.getServiceId() + "'");
                    List<TSUser> hql = systemService.findHql("from TSUser where id='" + cbsEquipmentApply.getApproveUser() + "'");
                    List<String> mobilePhoneList = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_base_user bu on u.id = bu.id where bu.username = '" + cbsEquipmentApply.getCreateBy() + "'");
                    String shortname = "";
                    if (shortnameList.size() != 0) {
                        shortname = shortnameList.get(0);
                    }
                    String emailTital = "备件发货超时提醒：<" + cbsDelinerHql.get(0).getCode() + ">-<" + cbsServiceList.get(0).getLevel() + ">-<" + cbsServiceList.get(0).getCode() + ">-<" + cbsServiceList.get(0).getZyCinemaName() + ">-" + getDateStr(new Date());
                    String emailContent = "Dear CSA同事们，发货单:<" + cbsDelinerHql.get(0).getCode() + ">尚未发货，请及时处理。\n" +
                            "发货单号：<" + cbsDelinerHql.get(0).getCode() + ">。\n" +
                            "备件申请人：<" + shortname + "> <" + cbsEquipmentApply.getCreateName() + "> <" + mobilePhoneList.get(0) + ">。\n" +
                            "备件审批人： <" + hql.get(0).getRealName() + "> <" + hql.get(0).getMobilePhone() + ">。\n" +
                            "具体内容请查看http://cbs.cfg-barco.com\n" +
                            "\n" +
                            "* 该邮件请勿回复";
                    List<String> listbySql = systemService.findListbySql("select email from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
                    for (int i = 0; i < listbySql.size(); i++) {
                        SendMailUtil.sendeMail(listbySql.get(i), emailTital, emailContent, systemService);
                    }
                    List<String> listbySql1 = systemService.findListbySql("select mobilePhone from t_s_user u LEFT JOIN t_s_role_user ru ON u.id=ru.userid LEFT JOIN t_s_role r ON ru.roleid=r.id LEFT JOIN t_s_base_user b on b.id = u.id where b.`status` = '" + Globals.User_Normal + "' and r.rolecode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
                	// add start 190125 Amy 9点30之前和21点之后不提醒
                	Calendar calendar = Calendar.getInstance();
                    calendar.setTime(new Date());
                    int hour = calendar.get(Calendar.HOUR_OF_DAY);
                    int minutes = calendar.get(Calendar.MINUTE);
                	if (hour < 9 || hour >= 21 || (hour == 9 && minutes < 30)) {
                	} else {
	                    for (int i = 0; i < listbySql1.size(); i++) {
	                        SendMessage.sendSMSWithContent(listbySql1.get(i), "发货单号<" + cbsDelinerHql.get(0).getCode() + ">尚未发货，请及时处理。详情请查看CBS系统", systemService);
	                    }
                	}
                	// add end 190125 Amy 9点30之前和21点之后不提醒
                }
            }
        }, StaticValue.selectStatusTiem);
    }

    //如果该备件申请关联的服务单没有SR号，则向SFDC发送创单邮件
    public void sendEmailToSFDC(CbsEquipmentApplyEntity cbsEquipmentApply) {
//        CbsServiceEntity cbsService = systemService.getEntity(CbsServiceEntity.class, cbsEquipmentApply.getServiceId());
//        if (StringUtil.isEmpty(cbsService.getSr())) {
//            List<CbsEquipmentApplyDetailEntity> cbsEquipmentApplyDetailHql = systemService.findHql("from CbsEquipmentApplyDetailEntity where applyId='" + cbsEquipmentApply.getId() + "'");
//            StringBuffer itemBuffer = new StringBuffer();
//            StringBuffer totalBuffer = new StringBuffer();
//            for (int i = 0; i < cbsEquipmentApplyDetailHql.size(); i++) {
//                itemBuffer.append("," + cbsEquipmentApplyDetailHql.get(i).getItem());
//                totalBuffer.append("," + cbsEquipmentApplyDetailHql.get(i).getTotal());
//            }
//            String s = "";
//            String s1 = "";
//            if (itemBuffer.toString().length() > 1) {
//            	s = itemBuffer.toString().substring(1, itemBuffer.toString().length());
//            }
//            if (totalBuffer.toString().length() > 1) {
//                s1 = totalBuffer.toString().substring(1, totalBuffer.toString().length());
//            }
//            List<String> snList = systemService.findListbySql("select type_desc from zy_equipment_type where id=(select type from zy_equipment_spare where sn='" + cbsService.getSn() + "')");
//            String serviceType1 = "";
//            String serviceType2 = "";
//            String errorType1 = "";
//            String errorType2 = "";
//            String sn = "";
//            if (snList.size() != 0 && StringUtil.isNotEmpty(snList.get(0))) {
//                sn = snList.get(0);
//            }
//            if (cbsService.getServiceType1() != null && cbsService.getServiceType2() != null) {
//                serviceType1 = cbsService.getServiceType1().getName();
//                serviceType2 = cbsService.getServiceType2().getName();
//            }
//            if (cbsService.getErrorType1() != null && cbsService.getErrorType2() != null) {
//                errorType1 = cbsService.getErrorType1().getName();
//                errorType2 = cbsService.getErrorType2().getName();
//            }
//            String bp = "";
//            List<String> bpList = systemService.findListbySql("select bp_no from zy_equipment_spare where sn='" + cbsService.getSn().trim() + "'");
//            if (bpList.size() != 0 && StringUtil.isNotEmpty(bpList.get(0))) {
//                bp = bpList.get(0);
//            }
//            String emailTital = "NewSR <" + serviceType1 + "," + serviceType2 + ">-<" + cbsService.getCode() + ">-<" + cbsService.getZyCinemaName() + ">";
//            String emailContent = " 影院名称：" + cbsService.getZyCinemaName() + "\n" +
//                    "服务单号：" + cbsService.getCode() + "\n" +
//                    "设备序列号：" + cbsService.getSn() + "\n" +
//                    "BP：" + bp + "\n" +
//                    "联系人：" + cbsService.getContactsName() + " - " + cbsService.getContactsPhone() + "\n" +
//                    "产品分类：" + sn + "\n" +
//                    "故障分类1：" + errorType1 + "\n" +
//                    "故障分类2：" + errorType2 + "\n" +
//                    "级别：" + cbsService.getLevel() + "\n" +
//                    "故障描述：" + cbsService.getDescription() + "\n" +
//                    "报修时间：" + cbsService.getCreateDate() + "\n" +
//                    "备件申请：备件号:" + s + " 数量: " + s1 + "";
//            SendMailUtil.sendeMail("service.DC.china@barco.com", emailTital, emailContent, systemService);
//            getSr(cbsService.getId());
//        }
    }

    /*获取Sr号*/
    public void getSr(final String id) {
        Timer timer = new Timer();
        timer.schedule(new TimerTask() {
            public void run() {
                try {
                    CbsServiceEntity cbsService = systemService.getEntity(CbsServiceEntity.class, id);
                    List<String> sr = SimpleMailReceiver.getSr();
                    String s = "";
                    for (int i = 0; i < sr.size(); i++) {
                        if (sr.get(i).contains("Barco Service Request") && sr.get(i).contains(cbsService.getCode())) {
                            s = sr.get(i).substring(22, 30);
                            break;
                        }
                    }
                    System.out.println("邮件解析出来的sr="+s);
                    log.debug("邮件解析出来的sr="+s);
                    cbsService.setSr(s);
                    systemService.saveOrUpdate(cbsService);
                    systemService.updateBySqlString("update cbs_equipment_apply set sr_code='" + s + "' where service_id='" + cbsService.getId() + "'");
                    systemService.updateBySqlString("update cbs_deliver set sr_code='" + s + "' where cr_code='" + cbsService.getCode() + "'");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }, 60000);
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsEquipmentApplyEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{record_id}", String.valueOf(t.getRecordId()));
        sql = sql.replace("#{service_id}", String.valueOf(t.getServiceId()));
        sql = sql.replace("#{sn}", String.valueOf(t.getSn()));
        sql = sql.replace("#{equipment_type}", String.valueOf(t.getEquipmentType()));
        sql = sql.replace("#{code}", String.valueOf(t.getCode()));
        sql = sql.replace("#{sr_code}", String.valueOf(t.getSrCode()));
        sql = sql.replace("#{zt}", String.valueOf(t.getZt()));
        sql = sql.replace("#{user_name}", String.valueOf(t.getUserName()));
        sql = sql.replace("#{phone}", String.valueOf(t.getPhone()));
        sql = sql.replace("#{cinema_name}", String.valueOf(t.getCinemaName()));
        sql = sql.replace("#{sheng}", String.valueOf(t.getSheng()));
        sql = sql.replace("#{shi}", String.valueOf(t.getShi()));
        sql = sql.replace("#{address}", String.valueOf(t.getAddress()));
        sql = sql.replace("#{warehouse_id}", String.valueOf(t.getWarehouseId()));
        sql = sql.replace("#{apply_reason}", String.valueOf(t.getApplyReason()));
        sql = sql.replace("#{memo}", String.valueOf(t.getMemo()));
        sql = sql.replace("#{status}", String.valueOf(t.getStatus()));
        sql = sql.replace("#{approve_user}", String.valueOf(t.getApproveUser()));
        sql = sql.replace("#{create_name}", String.valueOf(t.getCreateName()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{update_name}", String.valueOf(t.getUpdateName()));
        sql = sql.replace("#{update_by}", String.valueOf(t.getUpdateBy()));
        sql = sql.replace("#{update_date}", String.valueOf(t.getUpdateDate()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }

    //当前日期转化为MMdd字符转
    public String getDateStr(Date date) {
        SimpleDateFormat df = new SimpleDateFormat("MMdd");
        String dateStr = df.format(date);
        return dateStr;
    }
}